package com.yueqian.sc.dao.impl;

import com.yueqian.sc.bean.Course;
import com.yueqian.sc.bean.CourseVo;
import com.yueqian.sc.dao.ICourseDao;
import com.yueqian.sc.db.DBHelper;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class CourseDao implements ICourseDao {
    /**
     * 添加课程
     *
     * @param course
     * @return
     */
    @Override
    public boolean addCourse(Course course) {
        Connection conn = null;
        PreparedStatement smt = null;
        try {
            // 1. 获取数据库连接
            conn = DBHelper.getConnection();
            // 2. 编写sql语句
            String sql = "insert into course(course_name,course_pic,teacher_id,textbook,credit,classHours) \n" +
                    "value(?,?,?,?,?,?)";
            // 3. 创建预处理对象
            smt = conn.prepareStatement(sql);
            // 4. 使用外部动态数据替换掉占位符?
            smt.setString(1, course.getCourseName());
            smt.setString(2,course.getCoursePic());
            smt.setInt(3,course.getTeacherId());
            smt.setString(4, course.getTextbook());
            smt.setInt(5, course.getCredit());
            smt.setInt(6, course.getClassHours());
            // 5. 执行sql,返回受影响的行数
            int row = smt.executeUpdate();// 增删改都用这个方法
            return row > 0 ? true : false;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            // 6. 关闭数据库连接
            DBHelper.close(smt, conn);
        }
        return false;
    }

    /**
     * 查询所有课程（支持模糊查询，支持分页）
     *
     * @param keywords   关键字（课程名称或者教师名称）
     * @param startIndex 开始索引
     * @param pageSize   每页显示的条数
     * @return
     */
    @Override
    public List<CourseVo> queryAll(String keywords, int startIndex, int pageSize) {
        Connection conn = null;
        PreparedStatement smt = null;
        ResultSet rs = null;
        List<CourseVo> list = null;

        try {
            // 1. 获取数据库连接
            conn = DBHelper.getConnection();
            // 2. 编写sql
            String sql = "select c.course_id,c.course_name,c.course_pic,c.teacher_id,c.textbook,c.credit,c.classHours,u.nickname \n" +
                    "  from course c,users u where c.teacher_id = u.user_id and c.del_flag = 0 and (c.course_name like ? or u.nickname like ?) limit ?,?";
            // 3. 创建预处理对象
            smt = conn.prepareStatement(sql);
            // 4. 使用外部传参替换掉占位符
            smt.setString(1,"%"+keywords+"%");
            smt.setString(2,"%"+keywords+"%");
            smt.setInt(3,startIndex);
            smt.setInt(4,pageSize);
            // 5. 执行sql返回结果集
            rs = smt.executeQuery();
            // 6. 遍历结果集
            if(rs!=null){
                // 创建集合
                list = new ArrayList<>();
                while (rs.next()){
                    CourseVo course = new CourseVo();
                    course.setCourseId(rs.getInt("course_id"));
                    course.setCourseName(rs.getString("course_name"));
                    course.setCoursePic(rs.getString("course_pic"));
                    course.setTeacherId(rs.getInt("teacher_id"));
                    course.setTextbook(rs.getString("textbook"));
                    course.setCredit(rs.getInt("credit"));
                    course.setClassHours(rs.getInt("classHours"));
                    course.setNickname(rs.getString("nickname"));
                    list.add(course);
                }
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            // 7. 关闭数据库
            DBHelper.close(rs,smt,conn);
        }

        return list;
    }

    /**
     * 查询所有未选课程（支持模糊查询，支持分页）
     * @param studentId  已登录学生的编号
     * @param keywords   关键字（课程名称或者教师名称）
     * @param startIndex 开始索引
     * @param pageSize   每页显示的条数
     * @return
     */
    @Override
    public List<CourseVo> queryUnSelectedAll(int studentId,String keywords, int startIndex, int pageSize) {
        Connection conn = null;
        PreparedStatement smt = null;
        ResultSet rs = null;
        List<CourseVo> list = null;

        try {
            // 1. 获取数据库连接
            conn = DBHelper.getConnection();
            // 2. 编写sql
            String sql = "select \n" +
                    "  tea.user_id teacher_id,\n" +
                    "  -- 老师编号\n" +
                    "  tea.nickname nickname,\n" +
                    "  -- 老师名称\n" +
                    "  c.course_id,\n" +
                    "  -- 课程编号\n" +
                    "  c.course_name,\n" +
                    "  -- 课程名称\n" +
                    "  c.textbook,\n" +
                    "  -- 配套教材\n" +
                    "  c.credit,\n" +
                    "  -- 学分\n" +
                    "  c.classHours -- 学时\n" +
                    "from\n" +
                    "  users tea,\n" +
                    "  course c \n" +
                    "where tea.user_id = c.teacher_id \n" +
                    "  and c.del_flag = 0 \n" +
                    "  and c.course_id not in \n" +
                    "  (select \n" +
                    "    c.course_id -- 课程编号\n" +
                    "  from\n" +
                    "    users stu,\n" +
                    "    users tea,\n" +
                    "    course c,\n" +
                    "    select_course sc \n" +
                    "  where stu.user_id = sc.student_id -- 建立学生和课程的关系\n" +
                    "    and c.course_id = sc.course_id -- 建立学生和课程的关系\n" +
                    "    and tea.user_id = c.teacher_id -- 建立课程和老师的关系\n" +
                    "    and stu.user_id = ? -- 绑定登录的学生\n" +
                    "    and c.del_flag = 0) -- not in()不包括 \n" +
                    "  and (c.course_name like ? or tea.nickname like ?) limit ?,? ";
            // 3. 创建预处理对象
            smt = conn.prepareStatement(sql);
            // 4. 使用外部传参替换掉占位符
            smt.setInt(1,studentId);
            smt.setString(2,"%"+keywords+"%");
            smt.setString(3,"%"+keywords+"%");
            smt.setInt(4,startIndex);
            smt.setInt(5,pageSize);
            // 5. 执行sql返回结果集
            rs = smt.executeQuery();
            // 6. 遍历结果集
            if(rs!=null){
                // 创建集合
                list = new ArrayList<>();
                while (rs.next()){
                    CourseVo course = new CourseVo();
                    course.setCourseId(rs.getInt("course_id"));
                    course.setCourseName(rs.getString("course_name"));
                    course.setTeacherId(rs.getInt("teacher_id"));
                    course.setTextbook(rs.getString("textbook"));
                    course.setCredit(rs.getInt("credit"));
                    course.setClassHours(rs.getInt("classHours"));
                    course.setNickname(rs.getString("nickname"));
                    list.add(course);
                }
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            // 7. 关闭数据库
            DBHelper.close(rs,smt,conn);
        }

        return list;
    }

    /**
     * 查询所有已选课程（支持模糊查询，支持分页）
     *
     * @param keywords   关键字（课程名称或者教师名称）
     * @param startIndex 开始索引
     * @param pageSize   每页显示的条数
     * @return
     */
    @Override
    public List<CourseVo> querySelectedAll(int studentId,String keywords, int startIndex, int pageSize) {
        Connection conn = null;
        PreparedStatement smt = null;
        ResultSet rs = null;
        List<CourseVo> list = null;

        try {
            // 1. 获取数据库连接
            conn = DBHelper.getConnection();
            // 2. 编写sql
            String sql = "select \n" +
                    "  -- 学生编号\n" +
                    "  stu.user_id student_id,\n" +
                    "  -- 老师编号\n" +
                    "  tea.user_id teacher_id,\n" +
                    "  -- 老师名称\n" +
                    "  tea.nickname nickname,\n" +
                    "  -- 课程编号\n" +
                    "  c.course_id,\n" +
                    "  -- 课程名称\n" +
                    "  c.course_name,\n" +
                    "  -- 配套教材\n" +
                    "  c.textbook,\n" +
                    "  -- 学分\n" +
                    "  c.credit,\n" +
                    "  -- 学时\n" +
                    "  c.classHours\n" +
                    "from\n" +
                    "  users stu,\n" +
                    "  users tea,\n" +
                    "  course c,\n" +
                    "  select_course sc \n" +
                    "where stu.user_id = sc.student_id \n" +
                    "  and -- 建立学生和课程的关系\n" +
                    "  c.course_id = sc.course_id \n" +
                    "  and -- 建立学生和课程的关系\n" +
                    "  tea.user_id = c.teacher_id \n" +
                    "  and -- 建立课程和老师的关系\n" +
                    "  stu.user_id = ? \n" +
                    "  and -- 绑定登录的学生\n" +
                    "  c.del_flag = 0 \n" +
                    "  and (\n" +
                    "    c.course_name like ? \n" +
                    "    or tea.nickname like ?\n" +
                    "  ) \n" +
                    "limit ?, ?";
            // 3. 创建预处理对象
            smt = conn.prepareStatement(sql);
            // 4. 使用外部传参替换掉占位符
            smt.setInt(1,studentId);
            smt.setString(2,"%"+keywords+"%");
            smt.setString(3,"%"+keywords+"%");
            smt.setInt(4,startIndex);
            smt.setInt(5,pageSize);
            // 5. 执行sql返回结果集
            rs = smt.executeQuery();
            // 6. 遍历结果集
            if(rs!=null){
                // 创建集合
                list = new ArrayList<>();
                while (rs.next()){
                    CourseVo course = new CourseVo();
                    course.setCourseId(rs.getInt("course_id"));
                    course.setCourseName(rs.getString("course_name"));
                    course.setTeacherId(rs.getInt("teacher_id"));
                    course.setTextbook(rs.getString("textbook"));
                    course.setCredit(rs.getInt("credit"));
                    course.setClassHours(rs.getInt("classHours"));
                    course.setNickname(rs.getString("nickname"));
                    list.add(course);
                }
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            // 7. 关闭数据库
            DBHelper.close(rs,smt,conn);
        }

        return list;
    }

    /**
     * 课程总条目数
     *
     * @param keywords
     * @return
     */
    @Override
    public int courseCount(String keywords) {
        Connection conn = null;
        PreparedStatement smt = null;
        ResultSet rs = null;
        try {
            // 1. 获取数据库连接
            conn = DBHelper.getConnection();
            // 2. 编写sql
            String sql = "select count(*) from course c,users u where c.teacher_id = u.user_id and c.del_flag = 0 and (c.course_name like ? or u.nickname like ?)";
            // 3. 创建预处理对象
            smt = conn.prepareStatement(sql);
            // 4. 使用外部传参替换掉占位符
            smt.setString(1,"%"+keywords+"%");
            smt.setString(2,"%"+keywords+"%");
            // 5. 执行sql返回结果集
            rs = smt.executeQuery();
            // 6. 遍历结果集
            if(rs!=null && rs.next()){
                return rs.getInt(1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            // 7. 关闭数据库
            DBHelper.close(rs,smt,conn);
        }

        return 0;
    }

    /**
     * 未选课程总条目数
     *
     * @param keywords
     * @return
     */
    @Override
    public int unSelectedcCourseCount(int studentId,String keywords) {
        Connection conn = null;
        PreparedStatement smt = null;
        ResultSet rs = null;
        try {
            // 1. 获取数据库连接
            conn = DBHelper.getConnection();
            // 2. 编写sql
            String sql = "select \n" +
                    "  count(*) \n" +
                    "from\n" +
                    "  users tea,\n" +
                    "  course c \n" +
                    "where tea.user_id = c.teacher_id \n" +
                    "  and c.del_flag = 0 \n" +
                    "  and c.course_id not in \n" +
                    "  (select \n" +
                    "    c.course_id -- 课程编号\n" +
                    "  from\n" +
                    "    users stu,\n" +
                    "    users tea,\n" +
                    "    course c,\n" +
                    "    select_course sc \n" +
                    "  where stu.user_id = sc.student_id -- 建立学生和课程的关系\n" +
                    "    and c.course_id = sc.course_id -- 建立学生和课程的关系\n" +
                    "    and tea.user_id = c.teacher_id -- 建立课程和老师的关系\n" +
                    "    and stu.user_id = ? -- 绑定登录的学生\n" +
                    "    and c.del_flag = 0) -- not in()不包括 \n" +
                    "  and (\n" +
                    "    c.course_name like ? \n" +
                    "    or tea.nickname like ?\n" +
                    "  )\n" +
                    "    ";
            // 3. 创建预处理对象
            smt = conn.prepareStatement(sql);
            // 4. 使用外部传参替换掉占位符
            smt.setInt(1,studentId);
            smt.setString(2,"%"+keywords+"%");
            smt.setString(3,"%"+keywords+"%");
            // 5. 执行sql返回结果集
            rs = smt.executeQuery();
            // 6. 遍历结果集
            if(rs!=null && rs.next()){
                return rs.getInt(1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            // 7. 关闭数据库
            DBHelper.close(rs,smt,conn);
        }

        return 0;
    }

    /**
     * 已选课程总条目数
     *
     * @param keywords
     * @return
     */
    @Override
    public int selectCourseCount(int studentId,String keywords) {
        Connection conn = null;
        PreparedStatement smt = null;
        ResultSet rs = null;
        try {
            // 1. 获取数据库连接
            conn = DBHelper.getConnection();
            // 2. 编写sql
            String sql = "select \n" +
                    "  count(*)\n" +
                    "from\n" +
                    "  users stu,\n" +
                    "  users tea,\n" +
                    "  course c,\n" +
                    "  select_course sc \n" +
                    "where stu.user_id = sc.student_id \n" +
                    "  and -- 建立学生和课程的关系\n" +
                    "  c.course_id = sc.course_id \n" +
                    "  and -- 建立学生和课程的关系\n" +
                    "  tea.user_id = c.teacher_id \n" +
                    "  and -- 建立课程和老师的关系\n" +
                    "  stu.user_id = ? \n" +
                    "  and -- 绑定登录的学生\n" +
                    "  c.del_flag = 0 \n" +
                    "  and (\n" +
                    "    c.course_name like ? \n" +
                    "    or tea.nickname like ?\n" +
                    "  ) ";
            // 3. 创建预处理对象
            smt = conn.prepareStatement(sql);
            // 4. 使用外部传参替换掉占位符
            smt.setInt(1,studentId);
            smt.setString(2,"%"+keywords+"%");
            smt.setString(3,"%"+keywords+"%");
            // 5. 执行sql返回结果集
            rs = smt.executeQuery();
            // 6. 遍历结果集
            if(rs!=null && rs.next()){
                return rs.getInt(1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            // 7. 关闭数据库
            DBHelper.close(rs,smt,conn);
        }

        return 0;
    }

    /**
     * 根据courseId修改课程信息
     *
     * @param course
     * @return
     */
    @Override
    public boolean updateCourse(Course course) {
        Connection conn = null;
        PreparedStatement smt = null;

        try {
            // 1. 获取数据库连接
            conn = DBHelper.getConnection();
            // 2. 编写修改的sql
            String sql = "update course set course_name=?,course_pic=?,teacher_id=?,textbook=?,credit=?,classHours=? where course_id =?";
            // 3. 创建预处理对象
            smt = conn.prepareStatement(sql);
            // 4. 使用外部动态的数据替换掉问号
            smt.setString(1, course.getCourseName());
            smt.setString(2,course.getCoursePic());
            smt.setInt(3, course.getTeacherId());
            smt.setString(4, course.getTextbook());
            smt.setInt(5, course.getCredit());
            smt.setInt(6, course.getClassHours());
            smt.setInt(7, course.getCourseId());
            // 5. 执行sql,返回受影响的行数
            int row = smt.executeUpdate();
            return row > 0 ? true : false;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            // 6. 关闭数据库连接
            DBHelper.close(smt, conn);
        }
        return false;
    }

    /**
     * 根据courseId删除课程（假删除）
     *
     * @param courseId
     * @return
     */
    @Override
    public boolean deleteCourse(int courseId) {
        Connection conn = null;
        PreparedStatement smt = null;

        try {
            // 1. 获取数据库连接
            conn = DBHelper.getConnection();
            // 2. 编写修改的sql
            String sql = "update course set del_flag = 1 where course_id = ?";
            // 3. 创建预处理对象
            smt = conn.prepareStatement(sql);
            // 4. 使用外部动态的数据替换掉问号
            smt.setInt(1,courseId);
            // 5. 执行sql,返回受影响的行数
            int row = smt.executeUpdate();
            return row > 0 ? true : false;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            // 6. 关闭数据库连接
            DBHelper.close(smt, conn);
        }
        return false;
    }
}
